<?php
/**
 * Excel操作类
 * @author Leo（沈仁贵）
 *
 */
class Excel
{
	/**
	 * 读取Execl文件数据
	 * @param $filepath Excel文件物理路径
	 * @return array  返回二维数组
	 */
	public static function Read($filepath)
	{
		include JANSEN_ROOT.'include/excel/PHPExcel.php';
		include JANSEN_ROOT.'include/excel/PHPExcel/Reader/IReader.php';
		include JANSEN_ROOT.'include/excel/PHPExcel/Reader/Excel2007.php';
		include JANSEN_ROOT.'include/excel/PHPExcel/Reader/Excel5.php';
		include JANSEN_ROOT.'include/excel/PHPExcel/Reader/Excel2003XML.php';
		$PHPExcel = new PHPExcel();
    	$PHPReader = new PHPExcel_Reader_Excel2007();
	    if(!$PHPReader->canRead($filepath)){

	        $PHPReader = new PHPExcel_Reader_Excel5();
	        if(!$PHPReader->canRead($filepath)){
				$PHPReader = new PHPExcel_Reader_Excel2003XML();
				if(!$PHPReader->canRead($filepath)){
					throw new Exception("无法打开文件");
				}
	        }
	    }

	    $PHPExcel = $PHPReader->load($filepath);
	    $currentSheet = $PHPExcel->getSheet(0);
	    $allColumn = $currentSheet->getHighestColumn();
	    $allRow = $currentSheet->getHighestRow();
		$data = array('numRows' => $allRow, 'numCols' => ord($allColumn) - ord('A') + 1, 'cels' => array());
		if(strlen($allColumn) > 1)
		{
			$data['numCols'] = (strlen($allColumn)-1) * 26 + ord(substr($allColumn, -1)) - ord('A') + 1;
		}
		for($currentRow = 1;$currentRow<=$allRow;$currentRow++){
	        for($col=0;$col<=$data['numCols'];$col++){
				$currentColumn = $col < 26 ? chr(65+$col) : chr(64+(int)($col/26)).chr(65+$col%26);
	           $address = $currentColumn.$currentRow;
	           $data['cels'][$currentRow-1][] = $currentSheet->getCell($address)->getValue();
	        }
	    }
		return $data;
	}

	public static function CsvRead($filepath)
	{
		$result = array('cels'=>array(),'numRows'=>0);
		function _map($v)
		{
			return iconv('GBK',"UTF-8",$v);
		}
		$handle = fopen($filepath,"r");
		while ($data = fgetcsv($handle, 1000, ",")) {
			$data = array_map("_map",$data);
			$result['cels'][] = $data;
			$result['numRows']++;
		}
		fclose($handle);
		return $result;
	}


	/**
	 * 将指定数据导出成Excel文件
	 * @param string $filename 导出的文件名
	 * @param array $data 要导出的二维数组数据
	 */
	public static function Export($filename , $data, $param = null)
	{
		include JANSEN_ROOT.'include/excel/PHPExcel.php';
		include JANSEN_ROOT.'include/excel/PHPExcel/Writer/IWriter.php';
		include JANSEN_ROOT.'include/excel/PHPExcel/Writer/Excel5.php';

		$isheet = 0;
		$pagesize = isset($param['pagesize']) ? $param['pagesize'] : 0;

			$objPHPExcel = new PHPExcel();
			$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
			$objPHPExcel->setActiveSheetIndex($isheet);
			$objActSheet = $objPHPExcel->getActiveSheet();
			$objActSheet->setTitle('Table'.$isheet);

			header("Pragma: public");
			header("Expires: 0");
			header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
			header("Content-Type:application/force-download");
			header("Content-Type:application/vnd.ms-execl");
			header("Content-Type:application/octet-stream");
			header("Content-Type:application/download");
			header('Content-Disposition:attachment;filename='.$filename);
			header("Content-Transfer-Encoding:binary");
			$i = 1;
			foreach($data as $v)
			{
				if($pagesize > 0 AND $i > $pagesize)
				{
					$isheet++;
					$objPHPExcel->createSheet();
					$objPHPExcel->setActiveSheetIndex($isheet);
					$objActSheet = $objPHPExcel->getActiveSheet();
					$objActSheet->setTitle('Table'.$isheet);
					$i = 1;
				}

				$j = 'A';
				foreach($v as $v2)
				{
						$objActSheet->setCellValueExplicit($j++ . $i, $v2 , PHPExcel_Cell_DataType::TYPE_STRING);   
				}
				$i++;
			}
			$objWriter->save('php://output');
	}
}
?>